﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public partial class Select_Select1 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        if (!IsPostBack)
        {
            FillStudentInGrid();
        }
    }
    #region StudentTableOperation
    private DataTable FetchAllStudentInfo()
    {
        string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
        SqlConnection conn = new SqlConnection(connStr);
        SqlDataAdapter dAd = new SqlDataAdapter();
        SqlCommand dCmd = new SqlCommand();
        DataTable dTable = new DataTable();
        try
        {
            dCmd.CommandText = "SelectAllStudents";
            dCmd.CommandType = CommandType.StoredProcedure;
            dCmd.Connection = conn;
            dAd.SelectCommand = dCmd;
            // now open the connection
            conn.Open();
            dAd.Fill(dTable);
            conn.Close(); // close the connection
            return dTable;
        }
        catch (Exception e)
        {
            throw e;
        }

        finally
        {
            dCmd.Clone();
            dAd.Dispose();
            conn.Dispose();
        }
    }

    private DataTable SearchStudents(string uin, string firstName, string lastName)
    {
        string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
        SqlConnection conn = new SqlConnection(connStr);
        SqlDataAdapter dAd = new SqlDataAdapter();
        SqlCommand dCmd = new SqlCommand();
        DataTable dTable = new DataTable();
        try
        {
            dCmd.CommandText = "StudentsSearch";
            dCmd.CommandType = CommandType.StoredProcedure;
            dCmd.Connection = conn;
            SqlParameter[] prms = new SqlParameter[3];

            prms[0] = new SqlParameter("@UIN", SqlDbType.VarChar, 15);
            prms[0].Value = uin;
            prms[1] = new SqlParameter("@FirstName", SqlDbType.VarChar, 30);
            prms[1].Value = firstName;
            prms[2] = new SqlParameter("@LastName", SqlDbType.VarChar, 30);
            prms[2].Value = lastName;
            dCmd.Parameters.AddRange(prms);
            dAd.SelectCommand = dCmd;
            // now open the connection
            conn.Open();
            dAd.Fill(dTable);
            conn.Close(); // close the connection

            return dTable;
        }
        catch (Exception e)
        {
            throw e;
        }

        finally
        {
            dCmd.Clone();
            dAd.Dispose();
            conn.Dispose();
        }
    }

    private void InsertStudentInfo(string uin, string firstName, string lastName, 
                                   string address1, string address2, string city,
                                   string state, string country, string zip, string phone, string email)
    {
        string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
        int result = 0;
        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand dCmd = new SqlCommand();

        try
        {
            dCmd.CommandText = "StudentCreate"; // name of the sp
            dCmd.CommandType = CommandType.StoredProcedure;
            dCmd.Connection = conn;
            SqlParameter[] prms = new SqlParameter[12];

            prms[0] = new SqlParameter("@UIN", SqlDbType.VarChar, 15);
            prms[0].Value = uin;
            prms[1] = new SqlParameter("@FirstName", SqlDbType.VarChar, 30);
            prms[1].Value = firstName;
            prms[2] = new SqlParameter("@LastName", SqlDbType.VarChar, 30);
            prms[2].Value = lastName;
            prms[3] = new SqlParameter("@Address1", SqlDbType.VarChar, 100);
            prms[3].Value = address1;
            prms[4] = new SqlParameter("@Address2", SqlDbType.VarChar, 100);
            prms[4].Value = address2;
            prms[5] = new SqlParameter("@City", SqlDbType.VarChar, 30);
            prms[5].Value = city;
            prms[6] = new SqlParameter("@State", SqlDbType.VarChar, 10);
            prms[6].Value = state;
            prms[7] = new SqlParameter("@Country", SqlDbType.VarChar, 20);
            prms[7].Value = country;
            prms[8] = new SqlParameter("@Zip", SqlDbType.VarChar, 15);
            prms[8].Value = zip;
            prms[9] = new SqlParameter("@Phone", SqlDbType.VarChar, 15);
            prms[9].Value = phone;
            prms[10] = new SqlParameter("@Email", SqlDbType.VarChar, 50);
            prms[10].Value = email;
            prms[11] = new SqlParameter("@StudentAutoId", SqlDbType.Int);
            prms[11].Direction = ParameterDirection.Output;

            // add the parameters into command
            dCmd.Parameters.AddRange(prms);
            // now open the connection
            conn.Open();
            result = dCmd.ExecuteNonQuery();
            conn.Close(); // close the connection
            // write message
            if (result.Equals(0))
                Master.MasterMessageBoxText = "Couldn't insert record";
            else
                Master.MasterMessageBoxText = "Record inserted successfully with Id :" + dCmd.Parameters["@StudentAutoId"].Value.ToString();

        }

        catch (Exception e)
        {
            throw e;
        }

        finally
        {
            dCmd.Dispose();
            conn.Dispose();
        }
    }

    private void UpdateStudentInfo(int studentAutoId, string uin, string firstName, string lastName,
                                   string address1, string address2, string city,
                                   string state, string country, string zip, string phone, string email)
    {
        string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
        int result = 0;
        SqlConnection conn = new SqlConnection(connStr);
        SqlCommand dCmd = new SqlCommand();

        try
        {
            dCmd.CommandText = "StudentUpdate"; // name of the sp
            dCmd.CommandType = CommandType.StoredProcedure;
            dCmd.Connection = conn;
            SqlParameter[] prms = new SqlParameter[12];
            prms[0] = new SqlParameter("@UIN", SqlDbType.VarChar, 15);
            prms[0].Value = uin;
            prms[1] = new SqlParameter("@FirstName", SqlDbType.VarChar, 30);
            prms[1].Value = firstName;
            prms[2] = new SqlParameter("@LastName", SqlDbType.VarChar, 30);
            prms[2].Value = lastName;
            prms[3] = new SqlParameter("@Address1", SqlDbType.VarChar, 100);
            prms[3].Value = address1;
            prms[4] = new SqlParameter("@Address2", SqlDbType.VarChar, 100);
            prms[4].Value = address2;
            prms[5] = new SqlParameter("@City", SqlDbType.VarChar, 30);
            prms[5].Value = city;
            prms[6] = new SqlParameter("@State", SqlDbType.VarChar, 10);
            prms[6].Value = state;
            prms[7] = new SqlParameter("@Country", SqlDbType.VarChar, 20);
            prms[7].Value = country;
            prms[8] = new SqlParameter("@Zip", SqlDbType.VarChar, 15);
            prms[8].Value = zip;
            prms[9] = new SqlParameter("@Phone", SqlDbType.VarChar, 15);
            prms[9].Value = phone;
            prms[10] = new SqlParameter("@Email", SqlDbType.VarChar, 50);
            prms[10].Value = email;
            prms[11] = new SqlParameter("@StudentAutoId", SqlDbType.Int);
            prms[11].Value = studentAutoId;

            // add the parameters into command
            dCmd.Parameters.AddRange(prms);
            // now open the connection
            conn.Open();
            result = dCmd.ExecuteNonQuery();
            conn.Close(); // close the connection
            // write message
            if (result.Equals(0))
                Master.MasterMessageBoxText = "Couldn't able to update record";
            else
                Master.MasterMessageBoxText = "Record Updated successfully";

        }

        catch (Exception e)
        {
            throw e;
        }

        finally
        {
            dCmd.Dispose();
            conn.Dispose();
        }
    }

    private void DeleteStudent(int studentAutoId)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ToString());
        SqlCommand dCmd = new SqlCommand();
        try
        {
            conn.Open();
            dCmd.CommandText = "StudentDelete";
            dCmd.CommandType = CommandType.StoredProcedure;
            dCmd.Parameters.Add("@StudentAutoID", SqlDbType.Int).Value = studentAutoId;
            dCmd.Connection = conn;
            int result = dCmd.ExecuteNonQuery();
            if (result.Equals(0))
                Master.MasterMessageBoxText = "Couldn't able to delete record";
            else
                Master.MasterMessageBoxText = "Record Deleted successfully";

        }

        catch (Exception e)
        {
            throw e;
        }

        finally
        {
            dCmd.Dispose();
            conn.Close();
            conn.Dispose();
        }

    }
    #endregion
    private void FillStudentInGrid()
    {
        try
        {
            DataTable dTable = FetchAllStudentInfo(); ;
            if (dTable.Rows.Count > 0)
            {
                StudentInfoGridView.DataSource = dTable;
                StudentInfoGridView.DataBind();
            }
            else
            {
                dTable.Rows.Add(dTable.NewRow());
                StudentInfoGridView.DataSource = dTable;
                StudentInfoGridView.DataBind();
                int TotalColumns = StudentInfoGridView.Rows[0].Cells.Count;
                StudentInfoGridView.Rows[0].Cells.Clear();
                StudentInfoGridView.Rows[0].Cells.Add(new TableCell());
                StudentInfoGridView.Rows[0].Cells[0].ColumnSpan = TotalColumns;
                StudentInfoGridView.Rows[0].Cells[0].Text = "No Record Found";
                Master.MasterMessageBoxText = Master.MasterMessageBoxText + "Operation Completed Sucessfully";
            }
        }
        catch (Exception e)
        {
            Master.MasterMessageBoxText = Master.MasterMessageBoxText + "--ERROR: " + e.Message;
        }
    }

    protected void StudentCreateButton_Click(object sender, EventArgs e)
    {
        //Not implemeted yet
        Master.MasterMessageBoxText = "Simple Create Functionality not yet provided";
    }
    protected void StudentUpdateButton_Click(object sender, EventArgs e)
    {
        //Not implemeted yet
        Master.MasterMessageBoxText = "Simple Update Functionality not yet provided";
    }
    protected void StudentSearchButton_Click(object sender, EventArgs e)
    {
        string uin = StudentUINTextBox.Text;
        string firstName = StudentFirstNameTextBox.Text;
        string lastName = StudentLastNameTextBox.Text;
        try
        {
            DataTable dTable = SearchStudents(uin,firstName,lastName); ;
            if (dTable.Rows.Count > 0)
            {
                StudentInfoGridView.DataSource = dTable;
                StudentInfoGridView.DataBind();
            }
            else
            {
                dTable.Rows.Add(dTable.NewRow());
                StudentInfoGridView.DataSource = dTable;
                StudentInfoGridView.DataBind();
                int TotalColumns = StudentInfoGridView.Rows[0].Cells.Count;
                StudentInfoGridView.Rows[0].Cells.Clear();
                StudentInfoGridView.Rows[0].Cells.Add(new TableCell());
                StudentInfoGridView.Rows[0].Cells[0].ColumnSpan = TotalColumns;
                StudentInfoGridView.Rows[0].Cells[0].Text = "No Record Found";
                Master.MasterMessageBoxText = Master.MasterMessageBoxText + "Operation Completed Sucessfully";
                //FacultyUpdatButton.Enabled = false;
                //FacultyDeleteButton.Enabled = false;
            }
        }
        catch (Exception ex)
        {
            Master.MasterMessageBoxText = Master.MasterMessageBoxText + "--ERROR: " + ex.Message;
        }
    }
    protected void StudentDeleteButton_Click(object sender, EventArgs e)
    {
        //Not implemeted yet
        Master.MasterMessageBoxText = "Simple Delete Functionality not yet provided";
    }
    protected void GridViewStudentInfo_RowEditing(object sender, GridViewEditEventArgs e)
    {
        StudentInfoGridView.EditIndex = e.NewEditIndex;
        FillStudentInGrid(); 
    }
    protected void GridViewStudentInfo_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        try
        {
            DeleteStudent(int.Parse(StudentInfoGridView.DataKeys[e.RowIndex].Values[0].ToString()));
            FillStudentInGrid();
        }
        catch (Exception ex)
        {
            Master.MasterMessageBoxText = Master.MasterMessageBoxText + "--Error: " + ex.Message;
        }
    }
    protected void GridViewStudentInfo_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        try
        {
            GridViewRow row = (GridViewRow)StudentInfoGridView.Rows[e.RowIndex];
            string txtNewUin = ((Label)row.FindControl("StudentUINGridLabelEdit")).Text;
            string txtNewFirstName = ((TextBox)row.FindControl("StudentFNGridTxtBox")).Text;
            string txtNewLastName = ((TextBox)row.FindControl("StudentLNGridTxtBox")).Text;
            string txtNewAdd1 = ((TextBox)row.FindControl("StudentAdd1GridEditTxtBox")).Text;
            string txtNewAdd2 = ((TextBox)row.FindControl("StudentAdd2GridEditTxtBx")).Text;
            string txtNewCity = ((TextBox)row.FindControl("StudentCityGridEditTxtBx")).Text;
            string txtNewState = ((TextBox)row.FindControl("StudentStateGridEditTxtBx")).Text;
            string txtNewCountry = ((TextBox)row.FindControl("StudentCountryGridEditTxtBx")).Text;
            string txtNewZip = ((TextBox)row.FindControl("StudentZipGridEditTxtBx")).Text;
            string txtNewPhone = ((TextBox)row.FindControl("StudentPhoneGridEditTextBx")).Text;
            string txtNewEmail = ((TextBox)row.FindControl("StudentEmailGridEditTxtBx")).Text;
            int studentAutoId = int.Parse(StudentInfoGridView.DataKeys[e.RowIndex].Values[0].ToString());
  
            UpdateStudentInfo(studentAutoId, txtNewUin, txtNewFirstName, txtNewLastName, txtNewAdd1, txtNewAdd2,
                           txtNewCity, txtNewState, txtNewCountry, txtNewZip, txtNewPhone, txtNewEmail);
            StudentInfoGridView.EditIndex = -1;
            FillStudentInGrid();
        }
        catch (Exception ex)
        {
            Master.MasterMessageBoxText = Master.MasterMessageBoxText + "--Error: " + ex.Message;
        }
    }
    protected void GridViewStudentInfo_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        StudentInfoGridView.EditIndex = -1;
        FillStudentInGrid(); 
    }
    protected void StudentInfoGridView_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        try
        {
            Master.MasterMessageBoxText = "";
            if (e.CommandName.Equals("AddNew"))
            {
                GridViewRow row = (GridViewRow)StudentInfoGridView.FooterRow;
                string txtNewUin = ((TextBox)row.FindControl("StudentUINGridFooterTxtBox")).Text;
                string txtNewFirstName = ((TextBox)row.FindControl("StudentFNNewGridTxtBox")).Text;
                string txtNewLastName = ((TextBox)row.FindControl("StudentLNNewGridTxtBox")).Text;
                string txtNewAdd1 = ((TextBox)row.FindControl("StudentAdd1GridFooterTxtBox")).Text;
                string txtNewAdd2 = ((TextBox)row.FindControl("StudentAdd2GridFooterTxtBx")).Text;
                string txtNewCity = ((TextBox)row.FindControl("StudentCityGridFooterTxtBx")).Text;
                string txtNewState = ((TextBox)row.FindControl("StudentStateGridFooterTxtBx")).Text;
                string txtNewCountry = ((TextBox)row.FindControl("StudentCountryGridFooterTxtBx")).Text;
                string txtNewZip = ((TextBox)row.FindControl("StudentZipGridFooterTxtBx")).Text;
                string txtNewPhone = ((TextBox)row.FindControl("StudentPhoneGridFooterTxtBx")).Text;
                string txtNewEmail = ((TextBox)row.FindControl("StudentEmailGridFooterTxtBx")).Text;

                InsertStudentInfo(txtNewUin, txtNewFirstName, txtNewLastName, txtNewAdd1, txtNewAdd2,
                                txtNewCity, txtNewState, txtNewCountry, txtNewZip, txtNewPhone, txtNewEmail);
                FillStudentInGrid();
            }

            if (e.CommandName.Equals("Select"))
            {

               // ClearFieldData();
                //(GridViewRow)FacultyInfoGridView.SelectedRow.FindControl(
                //int selectedIndex = int.Parse(e.CommandArgument.ToString());
                //GridViewRow row = (GridViewRow)StudentInfoGridView.Rows[int.Parse(e.CommandArgument.ToString())];
                //if (row != null)
                //{
                //    FacultyAutoIdHiddenField.Value = FacultyInfoGridView.DataKeys[selectedIndex].Values[0].ToString();
                //    FacultyNameTextBox.Text = ((Label)row.FindControl("FacultyNameGridViewLabel")).Text;
                //    FacultyDesignationTxtBx.Text = ((Label)row.FindControl("FacultyDesignationGridViewLabel")).Text;
                //    FacultyEmailTxtBox.Text = ((Label)row.FindControl("FacultyEmailGridViewLabel")).Text;
                //    FacultyPhoneTxtBx.Text = ((Label)row.FindControl("FacultyPhoneGridViewLabel")).Text;
                //    FacultyLocationTxtBx.Text = ((Label)row.FindControl("FacultyLocationGridViewLabel")).Text;
                //    FacultyUpdatButton.Enabled = true;
                //    FacultyDeleteButton.Enabled = true;
                //}

                //FillFacultyInGrid();
                Master.MasterMessageBoxText = Master.MasterMessageBoxText + "Select Functionality NotImplemeted yet for student";
            }
        }
        catch (Exception ex)
        {
            Master.MasterMessageBoxText = Master.MasterMessageBoxText + "--Error: " + ex.Message;
        }
    }
}